package com.ncloud.timetask.dashboard.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author sundelong
 * project_name: timetask 
 * type_name: DashboardChnlTaskMapper 
 * date: 2017年6月21日下午4:56:48 
 * 
 */
@Mapper
public interface DashboardChnlTaskMapper {

	//所有渠道每月结算的收入
	@Select(value=("select a.chnl_id,sum(b.balance) as sum from sys_sales a left join ( select * from ("
			+ "select discnt_id,discnt_inst_id,create_user,balance,create_time from bs_discnt_fee  UNION"
			+ " select c.discnt_id,c.discnt_inst_id,c.create_user,c.balance,c.create_time from "
			+ "bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id) a where date_format(create_time,"
			+ "'%Y%m') = date_format(curdate(),'%Y%m')) b on a.user_id = b.create_user  group by chnl_id"))
	public List<Map<Object,Object>> getSumChnlByMonth();
	
	//所有销售销售业务的统计 销售ID，销售name，业务名称，销售额
	@Select(value = ("select e.user_id,e.sale_name,sale.prod_name,sale.fee from (select a.name as sale_name,"
			+ "b.user_id from sys_user a,sys_sales b where a.id=b.user_id and a.user_type='2' "
			+ " )e left join  (SELECT b.create_user,f_get_prodName(a.prod_id) AS prod_name,SUM(b.fee)"
			+ " AS fee FROM  (SELECT create_user,order_id,prod_id FROM bs_o_order UNION SELECT create_user,"
			+ "order_id,prod_id FROM bs_oh_order  ) a ,(SELECT order_id,create_user,received_fee as fee "
			+ "FROM bs_o_discnt_fee ) b WHERE a.order_id = b.order_id GROUP BY a.create_user,a.prod_id)sale"
			+ " on sale.create_user=e.user_id "))
	public List<Map<Object,Object>> getSaleProductFee();
	
	//查询数据是否存在
	@Select(value = ("select count(*) as count from bs_sale_product_count where task_id =#{0} and "
			+ "prod_name is null and user_id=#{2}"))
	public int getExistenceByUserIdNull(String taskId,String prodName,String userId);
	
	//查询数据是否存在
	@Select(value = ("select count(*) as count from bs_sale_product_count where task_id =#{0} and "
			+ "prod_name = #{1} and user_id=#{2}"))
	public int getExistenceByUserId(String taskId,String prodName,String userId);
	
	//将所有销售销售业务额插入bs_sale_product_count表
	@Insert(value = ("insert into bs_sale_product_count (user_id,task_id,sale_name,prod_name,count, "
			+ "update_time,remark) values(#{0},#{1},#{2},#{3},#{4},now(),#{5})"))
	public int insertProductCount(String userId,String taskId,String saleName,String prodName,String count,String remark);
	
	//更新数据
	@Update(value = ("update bs_sale_product_count set count = #{0},prod_name = #{1},update_time=now() where user_id = #{2}"
			+ " and prod_name is null"))
	public int updateProductCountNull(String count,String prodName,String userId);
		
	//更新数据
	@Update(value = ("update bs_sale_product_count set count = #{0},update_time=now() where user_id = #{1} and prod_name = #{2}"))
	public int updateProductCount(String count,String userId,String prodName);
		
	
	//查询数据是否存在
	@Select(value = ("select count(*) as count from bs_chnl_income_monthly where task_id =#{0} and"
			+ " year =#{1} and  month =#{2} and chnl_id =#{3}"))
	public int getExistence(String taskId,String year,String month,String chnlId);
	
	//将渠道每月销量插入bs_chnl_income_monthly表
	@Insert(value = ("insert into bs_chnl_income_monthly (task_id,year,month,chnl_id,chnl_income,"
			+ "update_time,remark) values(#{0},#{1},#{2},#{3},#{4},now(),#{5})"))
	public int insertChnlIncome(String taskId,String year,String month,String chnlId,String chnlIncome,String remark);
	
	//更新本月的数据
	@Update(value = ("update bs_chnl_income_monthly set chnl_income = #{0},update_time=now() where task_id = #{1} "
			+ "and year = #{2} and month = #{3} and chnl_id = #{4}"))
	public int updateBsMonthlyTask(String chnlIncome,String taskId,String year,String month,String chnlId );
		
}
